<?php
/*
 * ebiz front
 *
 * Customer Model
 *
 * @author li.jf
 *
 */

require_once '../app/core/models/BaseDao.php';
require_once '../app/core/db/DBUtil.class.php';

class OrderDao extends BaseDao
{
    private $tableName = "orders";

    public function __construct($dbUtil){
        parent::__construct($dbUtil);
        $this->_name = $this->tableName;
    }

    //get the last five order info by customerid
    public function getLastOrderInfoByCustomerId($id){
    	$sql="select o.id,o.order_num,o.created,o.shipping_address_id,o.total,o.status,a.first_name,a.last_name
    	from $this->tableName o,addresses a
    	where o.customer_id=a.customer_id and o.customer_id=? and o.shipping_address_id=a.id  and o.is_delete=0 and o.created > adddate(now(),interval -6 month)
    	order by o.created desc
    	limit 0,10 ";
    	$param = array($id);
        return $this->dbUtil->SelectData($sql, $param);
    }


    //get the all order info include address by customerid
    public function getAllOrdersByCustomerId($id){
    	$sql="select * from $this->tableName ,addresses where $this->tableName.customer_id=addresses.customer_id and $this->tableName.customer_id=? and $this->tableName.shipping_address_id=addresses.id and $this->tableName.is_delete=0 order by $this->tableName.created desc";
    	$param = array($id);
        return $this->dbUtil->SelectData($sql, $param);
    }

    //pagesize default is 2 set in page.php
    public function getOrderByOid($id,$offset,$pagesize){
    	$param = array($id);
    	$sql="select o.id,o.order_num,o.modified,o.shipping_address_id,o.total,o.status,a.first_name,a.last_name from $this->tableName o,addresses a where o.customer_id=a.customer_id and o.customer_id=? and o.shipping_address_id=a.id  and o.is_delete=0 order by o.created desc limit $offset, $pagesize ";
    	return $this->dbUtil->SelectData($sql, $param);
    }
    
    public function getOrderById($id){
    	$param = array($id);
    	$sql="select * from $this->tableName where id=? and is_delete=0 ";
    	return $this->dbUtil->SelectData($sql, $param);
    }

    //get the order by nearly 2 years
    public function getOrderByOidAndDateDesc($id){
    	$param = array($id);
    	$sql="select o.id,o.order_num,o.created,o.shipping_address_id,o.total,o.status,a.first_name,a.last_name
        from $this->tableName o,addresses a
        where o.customer_id=a.customer_id and o.customer_id=? and o.shipping_address_id=a.id  and o.is_delete=0 and  o.created > adddate(now(),interval -2 year)
        order by o.created desc
        limit 0, 10 ";
    	return $this->dbUtil->SelectData($sql, $param);
    }

    //get one orderinfo by customerId and OrderId
    public function getOrderByCustomerIdAndOrderId($id,$orderid){
		$sql="select * from $this->tableName where $this->tableName.customer_id=? and $this->tableName.id=? and $this->tableName.is_delete=0";
		$param = array($id,$orderid);
		return $this->dbUtil->SelectData($sql, $param);
    }

	/**
	 * @author allen
	 */
    public function saveOrder($param){
        $sql = "insert into $this->tableName
        (order_num, user_id, status, shipping_fee, discount_fee, tax_fee, merchandise_total, total, ship_profit_total, order_profit_total, shipping_time, shipping_expect, payment_method,
        invoice, agent_campus_id, agent_id, delivery_name, delivery_phone1, delivery_phone2, delivery_address_1, delivery_address_2, delivery_address_3, delivery_detail_address,
        delivery_email, remark, is_returned, create_id, modify_id, modified, created)
        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now())";
        return $this->dbUtil->ExecuteQuery($sql, $param);
    }

    /**
     * update order status
     */
    public function updateOrder($id, $order){
    	$param = array($order, $id);
        $sql = "update $this->tableName set status = ? where id = ? and is_delete=0";
        return $this->dbUtil->ExecuteQuery($sql, $param);
    }

    /**
     * get the order info and address info from area_dicts table
     */
    public function getOrderAndAddressInfoByOrderId($orderId){
    	$param = array($orderId);
    	$sql = "select orders.* ,
                ( SELECT `area_name` FROM `agent_campuses` WHERE `id` = `orders`.`agent_campus_id` ) AS `college_name`,
    	        ( select name from users where id=orders.agent_id ) as agent_name,
				( select name from users where id=orders.user_id  ) as username ,
				( select title from area_dicts where id=orders.delivery_address_1  ) as address1 ,
				( select title from area_dicts where id=orders.delivery_address_2  ) as address2  ,
				( select title from area_dicts where id=orders.delivery_address_3  ) as address3
				from orders
				where orders.id=?";
    	return $this->dbUtil->SelectData($sql, $param);
    }

    /**
     * Update order.status
     *
     * @param int $int_orderid
     * @param int $int_status
     * @param int $int_userid
     * @return boolean
     */
    public function updateOrderStatus( $int_orderid, $int_status, $int_userid ){
    	$arr_param = array( $int_status, $int_userid, $int_orderid );
    	$str_sql = "
    	   UPDATE `{$this->tableName}` SET
    	       `status` = ?,
    	       `modify_id` = ?,
    	       `modified` = NOW()
    	   WHERE `id` = ?
    	";
    	return $this->dbUtil->ExecuteQuery( $str_sql, $arr_param );
    }

    /**
     * get the order info through by cid and orderid
     *
     * @desc updated by M.Wang, add extention data with order
     * @param int $orderId
     * @return array
     */
    public function getOrderInfoById($orderId){
        $sql="
            SELECT
                `o`.*,
                ( SELECT `name` FROM `users` WHERE `id` = `o`.`user_id` ) AS `user_name`,
                ( SELECT `name` FROM `users` WHERE `id` = `o`.`agent_id` ) AS `agent_name`,
                ( SELECT `area_name` FROM `agent_campuses` WHERE `id` = `o`.`agent_campus_id` ) AS `college_name`,
                ( SELECT `title` FROM `area_dicts` WHERE `id` = `o`.`delivery_address_1` ) AS `province`,
                ( SELECT `title` FROM `area_dicts` WHERE `id` = `o`.`delivery_address_2` ) AS `city`,
                ( SELECT `title` FROM `area_dicts` WHERE `id` = `o`.`delivery_address_3` ) AS `county`
            FROM `{$this->tableName}` AS `o`
            WHERE `o`.`id` = ? AND `o`.`is_delete` = 0
        ";
        $param = array($orderId);
        return $this->dbUtil->SelectData($sql, $param);
    }

    /**
     * Get orders by angent id
     *
     * @author by M.Wang
     * @param int $int_id
     * @param int $int_start
     * @param int $int_limit
     * @param string $str_otherCondition
     * @return array
     */
    public function getOrdersByAgentId( $int_id, $int_start=null, $int_limit=null, $str_otherCondition = '', $bool_countOnly = false ){
    	$str_sql = "SELECT " . ($bool_countOnly ? 'SQL_CALC_FOUND_ROWS `o`.`id`,`o`.`created`  ' : "
    	       `o`.*,
    	       `p`.`title` AS `product_name`,
    	       ( SELECT `name` FROM `users` WHERE `id` = `o`.`user_id` ) AS `user_name`,
    	       ( SELECT `name` FROM `users` WHERE `id` = `o`.`agent_id` ) AS `agent_name`") .
    	   "FROM `{$this->tableName}` AS `o`
    	       JOIN `order_items` AS `i` ON `i`.`order_id` = `o`.`id`
    	       LEFT JOIN `product_items` AS `pi` ON `i`.`item_id` = `pi`.`id`
    	       LEFT JOIN `products` AS `p` ON `pi`.`product_id` = `p`.`id`
    	   WHERE `o`.`agent_id` = ?
    	       AND `o`.`is_delete` = 0
    	       {$str_otherCondition}";
    	  // if( !$bool_countOnly )
    	       $str_sql .= " GROUP BY `o`.`id` ORDER BY `o`.`created` DESC ";
    	   if( !is_null($int_start) && !empty($int_limit) )
    	       $str_sql .= "LIMIT {$int_start}, {$int_limit}";
    	return $this->dbUtil->SelectData( $str_sql, array( $int_id ) );
    }

    /**
     * Get orders by user id
     *
     * @author by M.Wang
     * @param int $int_id
     * @param int $int_start
     * @param int $int_limit
     * @param string $str_otherCondition
     * @return array
     */
    public function getOrdersByUserId( $int_id, $int_start=null, $int_limit=null, $str_otherCondition = '', $bool_countOnly = false ){
        $str_sql = "SELECT " . ($bool_countOnly ? "SQL_CALC_FOUND_ROWS `o`.`id`,`o`.`created` " :
               "`o`.*,
               `p`.`title` AS `product_name`,
               ( SELECT `name` FROM `users` WHERE `id` = `o`.`user_id` ) AS `user_name`,
               ( SELECT `name` FROM `users` WHERE `id` = `o`.`agent_id` ) AS `agent_name`") .
           "FROM `{$this->tableName}` AS `o`
               JOIN `order_items` AS `i` ON `i`.`order_id` = `o`.`id`
               LEFT JOIN `product_items` AS `pi` ON `i`.`item_id` = `pi`.`id`
               LEFT JOIN `products` AS `p` ON `pi`.`product_id` = `p`.`id`
           WHERE `o`.`user_id` = ?
               AND `o`.`is_delete` = 0
               {$str_otherCondition}";
        // if( !$bool_countOnly )
            $str_sql.= "  GROUP BY `o`.`id` ORDER BY `o`.`created` DESC";
         if( !is_null($int_start) && !empty($int_limit) )
            $str_sql.= " LIMIT {$int_start}, {$int_limit}";
        return $this->dbUtil->SelectData( $str_sql, array( $int_id ) );
    }

    /**
     * Update orders.is_returned
     *
     * @param int $orderId
     * @param int $returned
     * @return boolean
     */
    public function updateOrderReturned($orderId,$returned){
        $sql = "
            UPDATE `{$this->tableName}` SET
                `is_returned` = '{$returned}',
                `modified` = NOW()
            WHERE `id` = '{$orderId}'
        ";
        return $this->dbUtil->ExecuteQuery($sql, array());
    }
    
    public function getOrderByOrderNo($orderNo){
    	$sql = "select * from orders where is_delete = 0 and order_num = ? ";
    	$param = array($orderNo);
    	return $this->dbUtil->SelectData($sql, $param);
    }
    
    public function updateOrderById($order,$id){
    	$where = "id = $id";
    	$this->update($order,$where);
    }
    
    /**
     * 根据用户id和item ids得到购买该商品的用户的订单信息
     */
    public function getOpinionUserInfoFirstByUserIdAndItemIds($userId, $itemIds){
    	$param = array($userId);
    	$sql = "select o.id as oid, o.shipping_expect , o.payment_method , o.created , 
                oi.id as oiid , oi.quantity , oi.item_id ,
                u.id as userid
                from  orders o
                join order_items oi  on oi.order_id = o.id and oi.item_id in( $itemIds )
                join users u on u.id = o.user_id
                where o.user_id = ?  and o.is_delete = 0
                order by o.created desc
                limit 0,1";
    	return $this->dbUtil->SelectData($sql, $param);
    }
    
    /**
     * 根据用户id得到用户之前购买的item的信息
     */
    public function getOrderItemInfoByUserId($userId){
    	$param = array($userId);
    	$sql = "select oi.item_id
				from orders o
				join order_items oi on oi.order_id = o.id
				where o.user_id = ? and o.is_delete = 0";
    	return $this->dbUtil->SelectData($sql, $param);
    }
    
    
    
}